﻿Imports System.Reflection
Imports System.ComponentModel
Imports System.Runtime.InteropServices
Imports System.Data.OleDb

Public Class HocSinh_Export_Import
    Public Sub Export(ByVal dt As BindingList(Of HocSinhDTO), ByVal sheetName As String, ByVal title As String)
        'Tạo các đối tượng Excel
        Dim oExcel As New Microsoft.Office.Interop.Excel.Application()
        Dim oBooks As Microsoft.Office.Interop.Excel.Workbooks
        Dim oSheets As Microsoft.Office.Interop.Excel.Sheets
        Dim oBook As Microsoft.Office.Interop.Excel.Workbook
        Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet

        'Tạo mới một Excel WorkBook
        oExcel.Visible = True
        oExcel.DisplayAlerts = False
        oExcel.Application.SheetsInNewWorkbook = 1
        oBooks = oExcel.Workbooks
        oBook = DirectCast(oExcel.Workbooks.Add(Type.Missing), Microsoft.Office.Interop.Excel.Workbook)
        oSheets = oBook.Worksheets
        oSheet = DirectCast(oSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
        oSheet.Name = sheetName

        ' Tạo tiêu đề cột
        Dim cl1 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("A3", "A3")
        cl1.Value2 = "Mã Học sinh"
        cl1.ColumnWidth = 11.0
        Dim cl2 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("B3", "B3")
        cl2.Value2 = "Họ Tên"
        cl2.ColumnWidth = 25.0
        Dim cl3 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("C3", "C3")
        cl3.Value2 = "Giới tính"
        cl3.ColumnWidth = 11.5
        Dim cl4 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("D3", "D3")
        cl4.Value2 = "Ngày sinh"
        cl4.ColumnWidth = 15.5
        Dim cl5 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("E3", "E3")
        cl5.Value2 = "Địa chỉ"
        cl5.ColumnWidth = 25.0
        Dim cl6 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("F3", "F3")
        cl6.Value2 = "Quê quán"
        cl6.ColumnWidth = 11.0
        Dim cl7 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("G3", "G3")
        cl7.Value2 = "Email"
        cl7.ColumnWidth = 25.0
        Dim cl8 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("H3", "H3")
        cl8.Value2 = "Tôn giáo "
        cl8.ColumnWidth = 11.0
        Dim cl9 As Microsoft.Office.Interop.Excel.Range = oSheet.Range("I3", "I3")
        cl9.Value2 = "Mã Lớp"
        cl9.ColumnWidth = 11.0

        Dim rowHead As Microsoft.Office.Interop.Excel.Range = oSheet.Range("A3", "I3")
        rowHead.Font.Bold = True

        ' Tạo mẳng đối tượng để lưu dữ toàn bồ dữ liệu trong DataTable,
        ' vì dữ liệu được được gán vào các Cell trong Excel phải thông qua object thuần.
        Dim arr As Object(,)
        ReDim arr(dt.Count - 1, 9)

        Dim rowIndex As Integer = 0
        For Each item As HocSinhDTO In dt
            arr(rowIndex, 0) = item.MaHocSinh

            arr(rowIndex, 1) = item.HoTen
            If item.GioiTinh = True Then
                arr(rowIndex, 2) = "Nam"
            Else
                arr(rowIndex, 2) = "Nữ"
            End If
            arr(rowIndex, 3) = item.NgaySinh.ToShortDateString()
            arr(rowIndex, 4) = item.DiaChi
            arr(rowIndex, 5) = item.QueQuan
            arr(rowIndex, 6) = item.Email
            arr(rowIndex, 7) = item.TonGiao
            arr(rowIndex, 8) = item.MaLop
            rowIndex = rowIndex + 1
        Next
        'Thiết lập vùng điền dữ liệu
        Dim rowStart As Integer = 4
        Dim columnStart As Integer = 1
        Dim rowEnd As Integer = rowStart + dt.Count - 1
        Dim columnEnd As Integer = 10
        ' Ô bắt đầu điền dữ liệu
        Dim g1 As Microsoft.Office.Interop.Excel.Range = DirectCast(oSheet.Cells(rowStart, columnStart), Microsoft.Office.Interop.Excel.Range)
        ' Ô kết thúc điền dữ liệu
        Dim g2 As Microsoft.Office.Interop.Excel.Range = DirectCast(oSheet.Cells(rowEnd, columnEnd), Microsoft.Office.Interop.Excel.Range)
        ' Lấy về vùng điền dữ liệu
        Dim range As Microsoft.Office.Interop.Excel.Range = oSheet.Range(g1, g2)

        'Điền dữ liệu vào vùng đã thiết lập
        range.Value2 = arr

        'Lưu dữ liệu 
        Using exportSaveFileDialog As New SaveFileDialog()
            exportSaveFileDialog.Title = "Select Excel File"
            exportSaveFileDialog.Filter = "Microsoft Office Excel Workbook(*.xlsx)|*.xlsx"
            If DialogResult.OK = exportSaveFileDialog.ShowDialog() Then
                Dim fullFileName As String = exportSaveFileDialog.FileName
                oBook.SaveAs(fullFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, System.Reflection.Missing.Value, Missing.Value, False, False, _
                Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlUserResolution, True, Missing.Value, Missing.Value, Missing.Value)
                oBook.Saved = True
                MessageBox.Show(" Export successfully", "Exported to Excel", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
        End Using
    End Sub

    Public Function ImportDSHS(ByVal stringPath As String) As DataTable
        Dim path As String = ""
        KillProcByName("excel.exe")
        path = System.IO.Path.GetFullPath(stringPath)
        Dim ExcelObj As New Microsoft.Office.Interop.Excel.Application()
        Dim theWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
        Dim strPath As String = path
        theWorkbook = ExcelObj.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _
        Type.Missing, Type.Missing, Type.Missing)
        Dim sheets As Microsoft.Office.Interop.Excel.Sheets = theWorkbook.Worksheets
        Dim worksheet As Microsoft.Office.Interop.Excel.Worksheet = DirectCast(sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
        'Get the reference of second worksheet
        Dim strWorksheetName As String = worksheet.Name
        'Get the name of worksheet.
        Dim conn As New OleDbConnection()
        conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = '" + path + "'" + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0"""
        Dim a As String = "SELECT * FROM [" + strWorksheetName + "$]"
        Dim command As New OleDbCommand(a, conn)
        Dim projectactivityDS As New DataSet()
        Dim adapter As New OleDbDataAdapter(command)
        adapter.Fill(projectactivityDS)
        Return projectactivityDS.Tables(0)
        theWorkbook.Close()
        ExcelObj.Quit()
        conn.Close()
        ' Call the garbage collector to collect and wait for finalizers to finish.
        GC.Collect()
        GC.WaitForPendingFinalizers()
        ' Release the COM objects that have been instantiated.
        While (System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets) <> 0)
        End While
        GC.Collect()
        GC.WaitForPendingFinalizers()
        Marshal.FinalReleaseComObject(theWorkbook)
        Marshal.FinalReleaseComObject(worksheet)
        Marshal.FinalReleaseComObject(sheets)
        Marshal.FinalReleaseComObject(ExcelObj)
    End Function

    Public Shared Function KillProcByName(ByVal strProcname As String) As Boolean
        Dim Result As Boolean = False
        Dim strTemp As String = strProcname.ToLower().Trim().Replace(".exe", String.Empty)
        For Each process__1 As Process In Process.GetProcesses()
            If process__1.ProcessName.ToLower() = strTemp Then
                process__1.Kill()
                Result = True
                Exit For
            End If
        Next
        Return Result
    End Function
End Class
